package com.kreao.korean.repository;

import com.kreao.korean.entity.StudyRecord;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface StudyRecordRepository extends JpaRepository<StudyRecord, Long> {
    
    Page<StudyRecord> findByUserId(Long userId, Pageable pageable);
    
    List<StudyRecord> findByUserIdAndStudyTimeBetween(Long userId, LocalDateTime start, LocalDateTime end);
    
    @Query("SELECT COUNT(sr) FROM StudyRecord sr WHERE sr.user.id = :userId AND sr.studyTime >= :date")
    Long countByUserIdAndStudyTimeAfter(@Param("userId") Long userId, @Param("date") LocalDateTime date);
    
    @Query("SELECT COUNT(sr) FROM StudyRecord sr WHERE sr.user.id = :userId AND sr.isCorrect = true AND sr.studyTime >= :date")
    Long countCorrectAnswersByUserIdAndDate(@Param("userId") Long userId, @Param("date") LocalDateTime date);
    
    @Query("SELECT sr.studyType, COUNT(sr) FROM StudyRecord sr WHERE sr.user.id = :userId GROUP BY sr.studyType")
    List<Object[]> getStudyTypeStatistics(@Param("userId") Long userId);
    
    @Query("SELECT DATE(sr.studyTime) as date, COUNT(sr) as count FROM StudyRecord sr WHERE sr.user.id = :userId AND sr.studyTime >= :startDate GROUP BY DATE(sr.studyTime) ORDER BY date")
    List<Object[]> getDailyStudyStatistics(@Param("userId") Long userId, @Param("startDate") LocalDateTime startDate);
    
    @Query("SELECT AVG(sr.responseTime) FROM StudyRecord sr WHERE sr.user.id = :userId AND sr.studyTime >= :date")
    Double getAverageResponseTime(@Param("userId") Long userId, @Param("date") LocalDateTime date);
    
    @Query("SELECT COUNT(DISTINCT DATE(sr.studyTime)) FROM StudyRecord sr WHERE sr.user.id = :userId AND sr.studyTime >= :startDate")
    Long getStudyDaysCount(@Param("userId") Long userId, @Param("startDate") LocalDateTime startDate);
    
    @Query("SELECT COUNT(sr) FROM StudyRecord sr WHERE sr.user.id = :userId AND sr.studyTime BETWEEN :startDate AND :endDate")
    Long countByUserIdAndStudyTimeBetween(@Param("userId") Long userId, @Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    @Query("SELECT COUNT(sr) FROM StudyRecord sr WHERE sr.user.id = :userId AND sr.isCorrect = true AND sr.studyTime BETWEEN :startDate AND :endDate")
    Long countCorrectAnswersByUserIdAndDateRange(@Param("userId") Long userId, @Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
} 